package cn.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.pojo.Books;
import cn.util.DBUtil;

public class BooksMapperImpl extends DBUtil implements BooksMaaper {

	@Override
	public List<Books> getBooksByName(String name) {
		String sql = "select * from books where 1=1  ";
		List<Object> param = new ArrayList<Object>();
		if (name != null && !"".equals(name)) {
			sql += " and name like ? ";
			param.add("%" + name + "%");
		}
		sql += ";";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<Books> list = new ArrayList<Books>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Books b = new Books();
				b.setId(st.getInt("id"));
				b.setAuthor(st.getString("author"));
				b.setName(st.getString("name"));
				b.setPage(st.getInt("page"));
				b.setPublish(st.getString("publish"));
				b.setPrice(st.getInt("price"));
				b.setTime(st.getTimestamp("time"));
				list.add(b);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return list;
	}

	@Override
	public List<Books> getBooksByAuthor(String author) {
		String sql = "select * from books where 1=1  ";
		List<Object> param = new ArrayList<Object>();
		if (author != null && !"".equals(author)) {
			sql += " and author like ? ";
			param.add("%" + author + "%");
		}
		sql += ";";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<Books> list = new ArrayList<Books>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Books b = new Books();
				b.setId(st.getInt("id"));
				b.setAuthor(st.getString("author"));
				b.setName(st.getString("name"));
				b.setPage(st.getInt("page"));
				b.setPublish(st.getString("publish"));
				b.setPrice(st.getInt("price"));
				b.setTime(st.getTimestamp("time"));
				list.add(b);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return list;
	}

	@Override
	public List<Books> getBooksByPublish(String publish) {
		String sql = "select * from books where 1=1  ";
		List<Object> param = new ArrayList<Object>();
		if (publish != null && !"".equals(publish)) {
			sql += " and publish like ? ";
			param.add("%" + publish + "%");
		}
		sql += ";";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<Books> list = new ArrayList<Books>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Books b = new Books();
				b.setId(st.getInt("id"));
				b.setAuthor(st.getString("author"));
				b.setName(st.getString("name"));
				b.setPage(st.getInt("page"));
				b.setPublish(st.getString("publish"));
				b.setPrice(st.getInt("price"));
				b.setTime(st.getTimestamp("time"));
				list.add(b);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return list;
	}

	@Override
	public int addBooks(Books b) {
		String sql = "insert into books(author,name,page,publish,price) values(?,?,?,?,?) ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setObject(1, b.getAuthor());
			ps.setObject(2, b.getName());
			ps.setObject(3, b.getPage());
			ps.setObject(4, b.getPublish());
			ps.setObject(5, b.getPrice());
			int i = ps.executeUpdate();
			st = ps.getGeneratedKeys();
			ct.commit();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return 0;
	}
	
}
